##################################################################################################################
# Code for Results in Appendix Section A1.2
##################################################################################################################
write.table(data.frame(Description=c("","","","Appendix Section A1.2: Indirect Evidence From Federal Lobbying Disclosures")),row.names=F,col.names=F,quote=F)

##################################################################################################################
# To run, please set the working directory to match the location of the folder containing all replication files.
##################################################################################################################
#setwd("Replication_PSRM_ST24")

#if not yet created, create "output" folder
if("output"%in%list.files()==F){dir.create("output")}

#clear environment
rm(list=ls());gc();gc();gc();gc()

#if not yet installed, install package
if(length(find.package("dplyr",quiet=T))==0){install.packages("dplyr")}

#Load packages
library(dplyr)

#Set POSIX locale
Sys.setlocale(locale="C")

#LDA data files from OpenSecrets downloaded from https://www.opensecrets.org/bulk-data 
#The following code requires file "lob_lobbying.txt" to be unzipped

#unzip
unzip("Data/Appendix_A1_2_and_A5_8/lobby_lda.zip",exdir="Data/Appendix_A1_2_and_A5_8") #N.B. requires approx. 725 MB

#load file
t7<-readLines("Data/Appendix_A1_2_and_A5_8/lobby_lda/lob_lobbying.txt")

#prepare / assemble data
t7_2<-lapply(t7,function(x) strsplit(x,"\\|"))
t7_2l<-unlist(lapply(t7_2,function(x) length(x[[1]])))

#not including records likely to be incomplete
t7r1<-t7_2[which(t7_2l==32)]
t7r2<-t7_2[which(t7_2l==34)]
t7r1m<-as.data.frame(matrix(unlist(t7r1),ncol=32,byrow=T),stringsAsFactors = F)
t7r2m<-as.data.frame(matrix(unlist(t7r2),ncol=34,byrow=T),stringsAsFactors = F)
t7r2m2<-t7r2m[,-c(7,9)]
colnames(t7r1m)<-paste("V",seq(1,ncol(t7r1m),1),sep="")
colnames(t7r2m2)<-paste("V",seq(1,ncol(t7r2m2),1),sep="")
t7c<-rbind(t7r1m,t7r2m2)
t7c<-t7c[,c(2,4,6,7,seq(8,12,2),13,seq(14,32,2))]
colnames(t7c)<-c("Uniqid", "Registrant_raw", "Registrant", "Isfirm", "Client_raw", "Client", "Ultorg", "Amount", "Catcod", "Source", "Self", "IncludeNSFS",
                 "Use","Ind","Year","Type","Typelong","Affiliate")
###

#filter to avoid unamended/duplicate amounts
t7ca<-filter(t7c,Use!="n")

#create numeric variable for amounts
t7ca$amount2<-as.numeric(trimws(gsub("\\,","",t7ca$Amount)))

#create indicator for lobbying firm / contract lobbying
t7ca$contract1<-as.numeric(grepl("y",t7ca$Isfirm)==T)

#create biennium variable
t7ca$biennium<-as.numeric(t7ca$Year)
t7ca$biennium[which(t7ca$biennium%%2==1)]<-t7ca$biennium[which(t7ca$biennium%%2==1)]+1

#indicator for observations
t7ca$count<-1

#aggregate to client-registrant-year level
t7caNZ2<-t7ca %>%
  filter(amount2>0&Year<2021) %>%
  group_by(Registrant=Registrant,Client=Client,Year=Year,contract1=contract1) %>%
  summarise(.groups="keep",sd_amountB=sd(amount2,na.rm=T),mean_amountB=mean(amount2,na.rm=T),count=sum(count))

#Section A1.2 Result (1):
write.table(data.frame(Description=c("","Results:")),row.names=F,col.names=F,quote=F)

write.table(data.frame(Description=c("","Proportion of client-lobbying firm (contract lobbying) relationships where amounts paid are unchanged across filings within a given year:")),row.names=F,col.names=F,quote=F)
print(round(length(which(t7caNZ2$sd_amountB[t7caNZ2$contract1==1]==0&t7caNZ2$count[t7caNZ2$contract1==1]>1&t7caNZ2$mean_amountB[t7caNZ2$contract1==1]>0))/length(t7caNZ2$sd_amountB[t7caNZ2$contract1==1]&t7caNZ2$count[t7caNZ2$contract1==1]>1&t7caNZ2$mean_amountB[t7caNZ2$contract1==1]>0),2))

write.table(data.frame(Description=c("","Proportion of clients' in-house payment amounts that were unchanged across filings within a given year:")),row.names=F,col.names=F,quote=F)
print(round(length(which(t7caNZ2$sd_amountB[t7caNZ2$contract1==0]==0&t7caNZ2$count[t7caNZ2$contract1==0]>1&t7caNZ2$mean_amountB[t7caNZ2$contract1==0]>0))/length(t7caNZ2$sd_amountB[t7caNZ2$contract1==0]&t7caNZ2$count[t7caNZ2$contract1==0]>1&t7caNZ2$mean_amountB[t7caNZ2$contract1==0]>0),2))

#aggregate to client-registrant-biennium (2-year period) level
t7caNZ<-t7ca %>%
  filter(amount2>0&Year<2021) %>%
  group_by(Registrant,Client,biennium,contract1) %>%
  summarise(.groups="keep",sd_amountB=sd(amount2,na.rm=T),mean_amountB=mean(amount2,na.rm=T),count=sum(count))

#Section A1.2 Result (2, fn. 5):
write.table(data.frame(Description=c("","Proportion of client-lobbying firm (contract lobbying) relationships where amounts paid are unchanged across filings within a given biennium:")),row.names=F,col.names=F,quote=F)
print(round(length(which(t7caNZ$sd_amountB[t7caNZ$contract1==1]==0&t7caNZ$count[t7caNZ$contract1==1]>1))/length(t7caNZ$sd_amountB[t7caNZ$contract1==1]&t7caNZ$count[t7caNZ$contract1==1]>1),2))

write.table(data.frame(Description=c("","Proportion of clients' in-house payment amounts that were unchanged across filings within a given biennium:")),row.names=F,col.names=F,quote=F)
print(round(length(which(t7caNZ$sd_amountB[t7caNZ$contract1==0]==0&t7caNZ$count[t7caNZ$contract1==0]>1))/length(t7caNZ$sd_amountB[t7caNZ$contract1==0]&t7caNZ$count[t7caNZ$contract1==0]>1),2))


